package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it.networkAssets;

import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.networkassets.NetworkAssetMaintenance;
import com.glsc.ngateway.common.api.platform.dto.it.networkassets.NetworkAssetMaintenanceDto;
import lombok.NonNull;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Repository
public interface NetworkAssetMaintenanceRepo extends JpaRepository<NetworkAssetMaintenance, Long> {

    @Query(value = "select * from t_gw_it_net_assets_network_asset h " +
            "where deleted = 0 " +
            "and if ( :#{#networkAssetMtDto.serialNum} is not null, h.serial_num like CONCAT('%', :#{#networkAssetMtDto.serialNum}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.vendor} is not null, h.vendor = :#{#networkAssetMtDto.vendor}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.model} is not null, h.model = :#{#networkAssetMtDto.model}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.os} is not null, h.os = :#{#networkAssetMtDto.os}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.version} is not null, h.version = :#{#networkAssetMtDto.version}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.status} is not null, h.status = :#{#networkAssetMtDto.status}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.site} is not null, h.site = :#{#networkAssetMtDto.site}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.rack} is not null, h.rack = :#{#networkAssetMtDto.rack}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.unit} is not null, h.unit like CONCAT('%', :#{#networkAssetMtDto.unit}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.manufactureDate} is not null, h.manufacture_date = :#{#networkAssetMtDto.manufactureDate}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.expiredDate} is not null, h.expired_date = :#{#networkAssetMtDto.expiredDate}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.assetNUM} is not null, h.asset_num like CONCAT('%', :#{#networkAssetMtDto.assetNUM}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.contractNUM} is not null, h.contract_num like CONCAT('%', :#{#networkAssetMtDto.contractNUM}, '%'), 1=1) " +
            "", nativeQuery = true)
    Page<NetworkAssetMaintenance> page(Pageable pageable, @Param("networkAssetMtDto") NetworkAssetMaintenanceDto networkAssetMtDto);

    @Query(value = "select * from t_gw_it_net_assets_network_asset h " +
            "where deleted = 0 " +
            "and if ( :#{#networkAssetMtDto.serialNum} is not null, h.serial_num like CONCAT('%', :#{#networkAssetMtDto.serialNum}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.vendor} is not null, h.vendor = :#{#networkAssetMtDto.vendor}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.model} is not null, h.model = :#{#networkAssetMtDto.model}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.os} is not null, h.os = :#{#networkAssetMtDto.os}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.version} is not null, h.version = :#{#networkAssetMtDto.version}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.status} is not null, h.status = :#{#networkAssetMtDto.status}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.site} is not null, h.site = :#{#networkAssetMtDto.site}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.rack} is not null, h.rack = :#{#networkAssetMtDto.rack}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.unit} is not null, h.unit like CONCAT('%', :#{#networkAssetMtDto.unit}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.manufactureDate} is not null, h.manufacture_date = :#{#networkAssetMtDto.manufactureDate}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.expiredDate} is not null, h.expired_date = :#{#networkAssetMtDto.expiredDate}, 1=1) " +
            "and if ( :#{#networkAssetMtDto.assetNUM} is not null, h.asset_num like CONCAT('%', :#{#networkAssetMtDto.assetNUM}, '%'), 1=1) " +
            "and if ( :#{#networkAssetMtDto.contractNUM} is not null, h.contract_num like CONCAT('%', :#{#networkAssetMtDto.contractNUM}, '%'), 1=1) " +
            "", nativeQuery = true)
    List<NetworkAssetMaintenance> queryAll(@Param("networkAssetMtDto") NetworkAssetMaintenanceDto param);

    void deleteById(Long id);

    @Query(value = "select count(*) from t_gw_it_net_assets_network_asset h " +
            "where deleted = 0 " +
            "and if ( :id is not null, h.id != :id, 1=1) " +
            "and h.serial_num = :serialNum " +
            "", nativeQuery = true)
    Integer existsBySerialNum(@Nullable @Param("id") Long id, @NonNull @Param("serialNum") String serialNum);

    @Query(value = "select vendor, count(*) as count " +
            "from t_gw_it_net_assets_network_asset " +
            "where deleted = 0 " +
            "group by vendor " +
            "", nativeQuery = true)
    List<Map<String, Integer>> gourpByVendor();

    @Query(value = "select status, count(*) as count " +
            "from t_gw_it_net_assets_network_asset " +
            "where deleted = 0 " +
            "group by status " +
            "", nativeQuery = true)
    List<Map<String, Integer>> gourpByStatus();

    @Query(value = "select count(*) as count " +
            "from t_gw_it_net_assets_network_asset " +
            "where deleted = 0 " +
            "and if( :startDate is not null, manufacture_date >= :startDate, 1=1 ) " +
            "and if( :endDate is not null, manufacture_date < :endDate, 1=1 ) " +
            "", nativeQuery = true)
    Integer gourpByManufactureateDate(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    List<NetworkAssetMaintenance> findAllBySerialNumIn(List<String> serialNumList);

}
